# -*- coding:utf-8 -*-
from com.zzh.utils.dbUtils import MysqlUtils
from com.zzh.utils.table2Json_v2 import Table2Json
import time

local_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))


class CityDao(object):
    def __init__(self):
        self.conn = MysqlUtils.get_mysql_connections_v2()

    def insertCity(self, info):
        """
        添加 city 信息
        :param info:
        :return:
        """
        cityName = info['cityName']
        cityZipcode = info['cityZipcode']
        provinceId = info['provinceId']

        sql = "insert into zzh_city (city_name, city_zipcode, province_id) VALUES " \
              "(%s, %s, %s)"
        cur = self.conn.cursor()
        rs = cur.execute(sql, [cityName, cityZipcode, provinceId])
        self.conn.commit()
        cur.close()
        self.conn.close()
        return rs

    def findCityById(self, cityId):
        """
        根据ID查询 city 信息
        :param cityId:
        :return:
        """
        sql = "select * from zzh_city WHERE city_id=%s"

        cur = self.conn.cursor()
        rs = cur.execute(sql, [cityId])
        jsons = None
        if rs:
            list0 = cur.fetchall()
            jsons = Table2Json.city_list_to_json(list0)
        cur.close()
        self.conn.close()
        return jsons

    def findCityByCondition(self, **kwargs):
        """
        city 表的条件查询
        :param kwargs:
        :return:
        """
        provinceId = kwargs['provinceId']

        sql = "select * from zzh_city WHERE province_id=%s"
        cur = self.conn.cursor()
        rs = cur.execute(sql, [provinceId])
        jsons = None
        if rs:
            list0 = cur.fetchall()
            jsons = Table2Json.city_list_to_json(list0)
        cur.close()
        self.conn.close()
        return jsons

    def deleteCity(self, idArray):
        """
        根据ID删除信息，可以传递一个或者多个ID，ID以数组形式传递
        :param idArray:
        :return:
        """
        sql = "delete from zzh_city WHERE city_id=%s"
        cur = self.conn.cursor()
        for idd in idArray:
            rs = cur.execute(sql, [idd])
        self.conn.commit()
        cur.close()
        self.conn.close()
        return rs

    def updateCity(self, info):
        """
        更新 city
        :param info:
        :return:
        """
        cityId = info['cityId']
        cityName = info['cityName']
        cityZipcode = info['cityZipcode']
        provinceId = info['provinceId']
        cityUpdateTime = local_time

        sql = "update zzh_city set city_name=%s,city_zipcode=%s,province_id=%s, city_updated_time=%s WHERE city_id=%s"
        cur = self.conn.cursor()
        rs = cur.execute(sql, [cityName,cityZipcode,provinceId, cityUpdateTime, cityId])
        self.conn.commit()
        cur.close()
        self.conn.close()
        return rs

    def selectCity(self,cityId):
        """
        根据ID查询，返回元组
        :param cityId:
        :return: data tuple
        """
        sql = "select * from zzh_city WHERE city_id=%s"
        cur = self.conn.cursor()
        rs = cur.execute(sql, [cityId])
        arr = cur.fetchone()
        cur.close()
        self.conn.close()
        return arr
